﻿-- =============================================
-- Author:		M.A
-- Create date: 05/11/2013
-- Description:	Inserts new passengers
-- =============================================
CREATE PROCEDURE [trUser].[sp_Passengers_INSERT_NewPassenger] 
	@PassengerName nvarchar(255),
	@PassengerEmail nvarchar(50) = NULL
	
		-- Add the parameters for the stored procedure here
AS
BEGIN
	BEGIN TRANSACTION
		BEGIN TRY
			
			DECLARE @New_IDUser bigint 
			DECLARE @New_IDPassenger bigint 
	 	
			-- Insert the User, then get the newly created identity
			INSERT INTO tblUsers(Name, Email, DateUpdated)
				VALUES (@PassengerName, @PassengerEmail, GETDATE())
				
			-- the new Identity
			SELECT @New_IDUser = @@IDENTITY
				
			-- create the new passenger
			INSERT INTO tblPassengers(IDUser, DateUpdated)
				VALUES (@New_IDUser, GETDATE())
				
			-- get the new passenger ID
			SELECT @New_IDPassenger = @@IDENTITY
				
			-- return the new Id and the error (0) info
			SELECT 
				@New_IDPassenger AS IDPassenger
				,@New_IDUser as IDUser
				,0 AS ErrorNumber
				,'' AS ErrorMessage; 
				
		END TRY
		BEGIN CATCH		-- Error happens
				SELECT 
					-1 AS IDPassenger
					,- 1 as IDUser
					,ERROR_NUMBER() AS ErrorNumber
					,ERROR_MESSAGE() AS ErrorMessage;
		
			-- rollback transaction
			IF @@TRANCOUNT > 0
				ROLLBACK TRANSACTION;
				
		END CATCH;
		
	-- commit transaction	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;						
END

-- TEST HERE
	/* 
		EXEC [sp_Passengers_INSERT_NewPassenger]
			@PassengerName='Mesmin Augustin',
			@PassengerEmail='mesmin@gmail.com',
	*/